package canglang.monitor.reports.service.impl;

import canglang.monitor.cases.model.*;
import canglang.monitor.cases.service.*;
import canglang.monitor.cases.to.*;
import canglang.monitor.core.base.util.DateUtil;
import canglang.monitor.core.base.properties.AppDefaultProperty;
import canglang.monitor.core.codelookup.service.CodeLookupMgr;
import canglang.monitor.dept.model.DeptModel;
import canglang.monitor.dept.service.DeptMgr;
import canglang.monitor.reports.service.ReportsMgr;
import canglang.monitor.reports.to.ReportDataTO;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.axis.NumberTickUnit;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultKeyedValuesDataset;

import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import java.util.List;


@SuppressWarnings("unchecked")
public class ReportsMgrImpl implements ReportsMgr {
    private CaseOneMgr caseOneMgr;
    private CaseTwoMgr caseTwoMgr;
    private CaseThreeMgr caseThreeMgr;
    private CaseFourMgr caseFourMgr;
    private CaseFiveMgr caseFiveMgr;
    private CaseSixMgr caseSixMgr;
    private CaseSevenMgr caseSevenMgr;
    private CaseEightMgr caseEightMgr;
    private CaseNineMgr caseNineMgr;
    private CaseTenMgr caseTenMgr;
    private DeptMgr deptMgr;
    private CodeLookupMgr codeLookupMgr;


    public static final String DUTY_CODE = "DUTY_LIST";
    public static final String RANK_CODE = "RANK_LIST";
    public static final String SEX_CODE = "SEX_LIST";
    public static final String EDUCATION_CODE = "EDUCATION_LIST";
    public static final String TRUE_CODE = "TRUE_FALSE_LIST";
    public static final String CASE_REASON_CODE = "CASE_REASON_LIST";
    public static final String NATIONALITY_CODE = "NATIONALITY_LIST";
    public static final String POLI_CODE = "POLI_LIST";
    public static final String CASE_WORK_POS_LIST = "CASE_WORK_POS_LIST";
    public static final String CASE_WORK_POS_LIST1 = "CASE_WORK_POS_1_LIST";
    public static final String CASE_WORK_POS_LIST2 = "CASE_WORK_POS_2_LIST";
    public static final String CASE_WORK_POS_LIST3 = "CASE_WORK_POS_3_LIST";
    public static final String CATE_LIST = "CATE_LIST";
    public static final String CASE_SOURCE_LIST = "CASE_SOURCE_LIST";

    public CaseThreeMgr getCaseThreeMgr() {
        return caseThreeMgr;
    }

    public void setCaseThreeMgr(CaseThreeMgr caseThreeMgr) {
        this.caseThreeMgr = caseThreeMgr;
    }

    public CaseFourMgr getCaseFourMgr() {
        return caseFourMgr;
    }

    public void setCaseFourMgr(CaseFourMgr caseFourMgr) {
        this.caseFourMgr = caseFourMgr;
    }

    public CaseFiveMgr getCaseFiveMgr() {
        return caseFiveMgr;
    }

    public void setCaseFiveMgr(CaseFiveMgr caseFiveMgr) {
        this.caseFiveMgr = caseFiveMgr;
    }

    public CaseSixMgr getCaseSixMgr() {
        return caseSixMgr;
    }

    public void setCaseSixMgr(CaseSixMgr caseSixMgr) {
        this.caseSixMgr = caseSixMgr;
    }

    public CaseSevenMgr getCaseSevenMgr() {
        return caseSevenMgr;
    }

    public void setCaseSevenMgr(CaseSevenMgr caseSevenMgr) {
        this.caseSevenMgr = caseSevenMgr;
    }

    public CaseEightMgr getCaseEightMgr() {
        return caseEightMgr;
    }

    public void setCaseEightMgr(CaseEightMgr caseEightMgr) {
        this.caseEightMgr = caseEightMgr;
    }

    public CaseNineMgr getCaseNineMgr() {
        return caseNineMgr;
    }

    public void setCaseNineMgr(CaseNineMgr caseNineMgr) {
        this.caseNineMgr = caseNineMgr;
    }


    public CaseTenMgr getCaseTenMgr() {
        return caseTenMgr;
    }

    public void setCaseTenMgr(CaseTenMgr caseTenMgr) {
        this.caseTenMgr = caseTenMgr;
    }

    public DeptMgr getDeptMgr() {
        return deptMgr;
    }

    public void setDeptMgr(DeptMgr deptMgr) {
        this.deptMgr = deptMgr;
    }

    public CaseOneMgr getCaseOneMgr() {
        return caseOneMgr;
    }

    public void setCaseOneMgr(CaseOneMgr caseOneMgr) {
        this.caseOneMgr = caseOneMgr;
    }


    public CaseTwoMgr getCaseTwoMgr() {
        return caseTwoMgr;
    }

    public void setCaseTwoMgr(CaseTwoMgr caseTwoMgr) {
        this.caseTwoMgr = caseTwoMgr;
    }

    public CodeLookupMgr getCodeLookupMgr() {
        return codeLookupMgr;
    }

    public void setCodeLookupMgr(CodeLookupMgr codeLookupMgr) {
        this.codeLookupMgr = codeLookupMgr;
    }

    public static String getFOLDER() {
        return FOLDER;
    }

    public static void setFOLDER(String FOLDER) {
        ReportsMgrImpl.FOLDER = FOLDER;
    }

    private static String FOLDER = "C:\\temp\\";

    public byte[] genereateReport(Date startDate, Date endDate, int departmentId, HashMap map) {
        byte[] buf = new byte[0];
        String fileName = getTempFileName(".xls");
        File file1 = new File(FOLDER);
        if (!file1.exists()) {
            file1.mkdir();
        }
        String tempinFilePath = FOLDER + fileName;
        File file = new File(tempinFilePath);
        try {
            jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(file);

            int j = 0;
            for (int i = 1; i < 11; i++) {
                if (map.containsKey(i)) {
                    String value = (String) map.get(i);
                    jxl.write.WritableSheet ws = wwb.createSheet(value, j);
                    writeSheet(ws, i, startDate, endDate, departmentId);
                }
                j++;
            }
            wwb.write();
            wwb.close();
            buf = readByteArrayOfSpecifiedFile(file);
            file.delete();
        } catch (Exception e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        return buf;
    }

    private byte[] readByteArrayOfSpecifiedFile(File file) throws IOException {
        FileInputStream fis = new FileInputStream(file);
        ByteArrayOutputStream fileByteStream = new ByteArrayOutputStream();
        int ch;
        while ((ch = fis.read()) != -1) {
            fileByteStream.write(ch);
        }
        fis.close();
        try {
            byte[] result = fileByteStream.toByteArray();
            return result;
        } finally {
            fileByteStream.close();
        }
    }


    private void writeSheet(jxl.write.WritableSheet ws, int i, Date startDate, Date endDate, int departmentId) {
        writeHeader(ws, i);
        writeDetail(ws, i, startDate, endDate, departmentId);
    }

    private void writeDetail(jxl.write.WritableSheet ws, int i, Date startDate, Date endDate, int departmentId) {
        if (i == 1) {
            SearchOneTO searchTO = new SearchOneTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseOneMgr.getCaseOneList(searchTO);
            Iterator itor = list.iterator();
            int row = 3;
            while (itor.hasNext()) {
                CaseOneModel model = (CaseOneModel) itor.next();
                writeOneDetail(ws, model, row);
                row++;
            }

        } else if (i == 2) {
            SearchTwoTO searchTO = new SearchTwoTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseTwoMgr.getCaseTwoList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseTwoModel model = (CaseTwoModel) itor.next();
                writeTwoDetail(ws, model, row);
                row++;
            }
        } else if (i == 3) {
            SearchThreeTO searchTO = new SearchThreeTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseThreeMgr.getCaseThreeList(searchTO);
            Iterator itor = list.iterator();
            int row = 3;
            while (itor.hasNext()) {
                CaseThreeModel model = (CaseThreeModel) itor.next();
                writeThreeDetail(ws, model, row);
                row++;
            }
        } else if (i == 4) {
            SearchFourTO searchTO = new SearchFourTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseFourMgr.getCaseFourList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseFourModel model = (CaseFourModel) itor.next();
                writeFourDetail(ws, model, row);
                row++;
            }
        } else if (i == 5) {
            SearchFiveTO searchTO = new SearchFiveTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseFiveMgr.getCaseFiveList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseFiveModel model = (CaseFiveModel) itor.next();
                writeFiveDetail(ws, model, row);
                row++;
            }
        } else if (i == 6) {
            SearchSixTO searchTO = new SearchSixTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            searchTO.setDeptId(departmentId);
            List list = caseSixMgr.getCaseSixList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseSixModel model = (CaseSixModel) itor.next();
                writeSixDetail(ws, model, row);
                row++;
            }
        } else if (i == 7) {
            SearchSevenTO searchTO = new SearchSevenTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            List list = caseSevenMgr.getCaseSevenList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseSevenModel model = (CaseSevenModel) itor.next();
                writeSevenDetail(ws, model, row);
                row++;
            }
        } else if (i == 8) {
            SearchEightTO searchTO = new SearchEightTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            List list = caseEightMgr.getCaseEightList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseEightModel model = (CaseEightModel) itor.next();
                writeEightDetail(ws, model, row);
                row++;
            }
        }else if (i == 9) {
            SearchNineTO searchTO = new SearchNineTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            List list = caseNineMgr.getCaseNineList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseNineModel model = (CaseNineModel) itor.next();
                writeNineDetail(ws, model, row);
                row++;
            }
        }
        else if (i == 10) {
            SearchTenTO searchTO = new SearchTenTO();
            searchTO.setDateFrom(startDate);
            searchTO.setDateTo(endDate);
            List list = caseTenMgr.getCaseTenList(searchTO);
            Iterator itor = list.iterator();
            int row = 2;
            while (itor.hasNext()) {
                CaseTenModel model = (CaseTenModel) itor.next();
                writeTenDetail(ws, model, row);
                row++;
            }
        }

    }

    private String getDeppartment(int departmentId) {
        String result = "";
        DeptModel deptModel = deptMgr.getDept(departmentId);
        if (deptModel != null) {
            result = deptModel.getDeptName();
        }
        return result;
    }

    private String getDate(Date dt) {
        String result = "";
        result = DateUtil.formatDate(dt);
        if (result == null)
            return "";
        return result.substring(6,10)+ AppDefaultProperty.getProperty("label.year") +result.substring(3,5)+AppDefaultProperty.getProperty("label.month")+result.substring(0,2)+AppDefaultProperty.getProperty("label.day");
    }

    private void writeOneDetail(jxl.write.WritableSheet ws, CaseOneModel model, int row) {
        jxl.write.Label labele = new jxl.write.Label(0, row, model.getName(), getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(1, row, getDescByCode(SEX_CODE, model.getSex()), getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(2, row, String.valueOf(model.getAge()), getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(3, row, getDescByCode(NATIONALITY_CODE, model.getNationality()), getCell(true));
        jxl.write.Label labeli = new jxl.write.Label(4, row, getDescByCode(EDUCATION_CODE, model.getEducation()), getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(5, row, getDeppartment(model.getDeptId()), getCell(true));
//        jxl.write.Label labelk = new jxl.write.Label(6, row, getDescByCode(DUTY_CODE, model.getDuty()), getCell(true));
        jxl.write.Label labell = new jxl.write.Label(6, row, getDescByCode(RANK_CODE, model.getRank()), getCell(true));
        jxl.write.Label labelm = new jxl.write.Label(7, row, getDescByCode(POLI_CODE, model.getPoliStatus()), getCell(true));
        jxl.write.Label labeln = new jxl.write.Label(8, row, getDescByCode(TRUE_CODE, model.getHndInd()), getCell(true));
        jxl.write.Label labelo = new jxl.write.Label(9, row, getDescByCode(TRUE_CODE, model.getCppccInd()), getCell(true));
        jxl.write.Label labelp = new jxl.write.Label(10, row, model.getWorkPosition(), getCell(true));
        jxl.write.Label labelq = new jxl.write.Label(11, row, getIndutryDescByCode(model.getIndustry1(), model.getIndustry2()), getCell(true));
        jxl.write.Label labelr = new jxl.write.Label(12, row, getDescByCode(TRUE_CODE, model.getHotInd()), getCell(true));
        jxl.write.Label labels = new jxl.write.Label(13, row, getDescByCode(CASE_SOURCE_LIST,model.getCaseSource()), getCell(true));
        jxl.write.Label labelt = new jxl.write.Label(14, row, getDate(model.getCaseDate()), getCell(true));
        jxl.write.Label labelu = new jxl.write.Label(15, row, getDescByCode(CASE_REASON_CODE, model.getCaseReason()), getCell(true));
        jxl.write.Label labelv = new jxl.write.Label(16, row, getDate(model.getFirstInvDate()), getCell(true));
        jxl.write.Label labelw = new jxl.write.Label(17, row, model.getFirstInvResult(), getCell(true));
        jxl.write.Label labelaa = new jxl.write.Label(18, row, getDate(model.getRegisterDate()), getCell(true));
        jxl.write.Label labelab = new jxl.write.Label(19, row, model.getCaseAmount(), getCell(true));
        jxl.write.Label labelac = new jxl.write.Label(20, row, getDate(model.getInvDate()), getCell(true));
        jxl.write.Label labelad = new jxl.write.Label(21, row, getDescByCode(CASE_REASON_CODE, model.getInvReason()), getCell(true));
        jxl.write.Label labelae = new jxl.write.Label(22, row, model.getInvAmount(), getCell(true));
        jxl.write.Label labelaf = new jxl.write.Label(23, row, model.getInvResult(), getCell(true));
        jxl.write.Label labelag = new jxl.write.Label(24, row, getDate(model.getSueDate()), getCell(true));
        jxl.write.Label labelah = new jxl.write.Label(25, row, getDate(model.getProsDate()), getCell(true));
        jxl.write.Label labelas = new jxl.write.Label(26, row, getDate(model.getJudgeDate()), getCell(true));
        jxl.write.Label labelai = new jxl.write.Label(27, row, model.getJudgeAccusal(), getCell(true));
        jxl.write.Label labelaj = new jxl.write.Label(28, row, model.getJudgeAmount(), getCell(true));
        jxl.write.Label labelak = new jxl.write.Label(29, row, model.getJudgePenalty(), getCell(true));
        jxl.write.Label labelal = new jxl.write.Label(30, row, model.getJudgeResult(), getCell(true));
        jxl.write.Label labelam = new jxl.write.Label(31, row, getDate(model.getFirstCrimeDate()), getCell(true));
        jxl.write.Label labelan = new jxl.write.Label(32, row, getDate(model.getLastCrimeDate()), getCell(true));
        jxl.write.Label labelao = new jxl.write.Label(33, row, model.getCrimeCount(), getCell(true));
        jxl.write.Label labelap = new jxl.write.Label(34, row, model.getCrimeMethod(), getCell(true));
        jxl.write.Label labelaq = new jxl.write.Label(35, row, model.getAriseSegment(), getCell(true));
        jxl.write.Label labelar = new jxl.write.Label(36, row, model.getAriseReason(), getCell(true));
        jxl.write.Label labelat = new jxl.write.Label(37, row, deNull(model.getUpload1FileName()), getCell(true));
        jxl.write.Label labelav = new jxl.write.Label(38, row, deNull(model.getUpload2FileName()), getCell(true));


        try {
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labeli);
            ws.addCell(labelj);
//            ws.addCell(labelk);
            ws.addCell(labell);
            ws.addCell(labelm);
            ws.addCell(labeln);
            ws.addCell(labelo);
            ws.addCell(labelp);
            ws.addCell(labelq);
            ws.addCell(labelr);
            ws.addCell(labels);
            ws.addCell(labelt);
            ws.addCell(labelu);
            ws.addCell(labelv);
            ws.addCell(labelw);
            ws.addCell(labelaa);
            ws.addCell(labelab);
            ws.addCell(labelac);
            ws.addCell(labelad);
            ws.addCell(labelae);
            ws.addCell(labelaf);
            ws.addCell(labelag);
            ws.addCell(labelah);
            ws.addCell(labelai);
            ws.addCell(labelaj);
            ws.addCell(labelak);
            ws.addCell(labelal);
            ws.addCell(labelam);
            ws.addCell(labelan);
            ws.addCell(labelao);
            ws.addCell(labelap);
            ws.addCell(labelaq);
            ws.addCell(labelar);
            ws.addCell(labelas);
            ws.addCell(labelat);
            ws.addCell(labelav);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeTwoDetail(jxl.write.WritableSheet ws, CaseTwoModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, model.getInfoSourcer(), getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, row, model.getDutyOfficer(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, row, getDeppartment(model.getDeptId()), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, row, model.getWorkPosition(), getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, row, model.getReferTerm(), getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, row, deNull(model.getUpload1FileName()), getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(6, row, deNull(model.getUpload2FileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeThreeDetail(jxl.write.WritableSheet ws, CaseThreeModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, model.getName(), getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, row, getDescByCode(SEX_CODE, model.getSex()), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, row, String.valueOf(model.getAge()), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, row, getDescByCode(NATIONALITY_CODE, model.getNationality()), getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, row, getDescByCode(EDUCATION_CODE, model.getEducation()), getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, row, getDeppartment(model.getDeptId()), getCell(true));
//         jxl.write.Label labelg = new jxl.write.Label(6, row, getDescByCode(DUTY_CODE, model.getDuty()), getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(6, row, getDescByCode(RANK_CODE, model.getRank()), getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(7, row, getDescByCode(TRUE_CODE, model.getMonitorInd()), getCell(true));
        jxl.write.Label labelk = new jxl.write.Label(8, row, getDescByCode(CATE_LIST, model.getBelongType()), getCell(true));
        jxl.write.Label labell = new jxl.write.Label(9, row, getDescByCode(TRUE_CODE, model.getHndInd()), getCell(true));
        jxl.write.Label labelm = new jxl.write.Label(10, row, getDescByCode(TRUE_CODE, model.getCppccInd()), getCell(true));
        jxl.write.Label labeln = new jxl.write.Label(11, row, model.getWorkPosition(), getCell(true));
        jxl.write.Label labelo = new jxl.write.Label(12, row, getIndutryDescByCode(model.getIndustry1(), model.getIndustry2()), getCell(true));
        jxl.write.Label labelp = new jxl.write.Label(13, row, getDescByCode(TRUE_CODE, model.getHotInd()), getCell(true));
        jxl.write.Label labelr = new jxl.write.Label(14, row, model.getCaseNature(), getCell(true));
        jxl.write.Label labels = new jxl.write.Label(15, row, model.getCaseResult(), getCell(true));
        jxl.write.Label labelt = new jxl.write.Label(16, row, model.getCaseFact(), getCell(true));
        jxl.write.Label labelu = new jxl.write.Label(17, row, model.getCrimeMethod(), getCell(true));
        jxl.write.Label labelv = new jxl.write.Label(18, row, model.getAriseSegment(), getCell(true));
        jxl.write.Label labelw = new jxl.write.Label(19, row, model.getAriseReason(), getCell(true));
        jxl.write.Label labelx = new jxl.write.Label(20, row, deNull(model.getUploadFileName()), getCell(true));
        jxl.write.Label labely = new jxl.write.Label(21, row, deNull(model.getUpload1FileName()), getCell(true));
        jxl.write.Label labelz = new jxl.write.Label(22, row, deNull(model.getUpload2FileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
//            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labelj);
            ws.addCell(labelk);
            ws.addCell(labell);
            ws.addCell(labelm);
            ws.addCell(labeln);
            ws.addCell(labelo);
            ws.addCell(labelp);
            ws.addCell(labelr);
            ws.addCell(labels);
            ws.addCell(labelt);
            ws.addCell(labelu);
            ws.addCell(labelv);
            ws.addCell(labelw);
            ws.addCell(labelx);
            ws.addCell(labely);
            ws.addCell(labelz);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeFourDetail(jxl.write.WritableSheet ws, CaseFourModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, getDeppartment(model.getFindDept()), getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, row, model.getInfoSource(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, row, model.getDutyOfficer(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, row, getDeppartment(model.getDeptId()), getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, row, model.getWorkPosition(), getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, row, model.getReferTerm(), getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(6, row, deNull(model.getUploadFileName()), getCell(true));
        jxl.write.Label labelk = new jxl.write.Label(7, row, deNull(model.getUpload1FileName()), getCell(true));
        jxl.write.Label labell = new jxl.write.Label(8, row, deNull(model.getUpload2FileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelj);
            ws.addCell(labelk);
            ws.addCell(labell);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeFiveDetail(jxl.write.WritableSheet ws, CaseFiveModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, model.getSource(), getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, row, getDeppartment(model.getDeptId()), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, row, model.getDuty(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, row, model.getCaseNature(), getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, row, getDate(model.getReportDate()), getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, row, model.getReportFact(), getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(6, row, getDescByCode(TRUE_CODE, model.getPplInd()), getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(7, row, getDescByCode(TRUE_CODE, model.getProcInd()), getCell(true));
        jxl.write.Label labeli = new jxl.write.Label(8, row, deNull(model.getUploadFileName()), getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(9, row, deNull(model.getUpload1FileName()), getCell(true));
        jxl.write.Label labelk = new jxl.write.Label(10, row, deNull(model.getUpload2FileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labeli);
            ws.addCell(labelj);
            ws.addCell(labelk);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeSixDetail(jxl.write.WritableSheet ws, CaseSixModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, model.getTitle(), getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, row, getDeppartment(model.getDeptId()), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, row, model.getContent(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, row, deNull(model.getUploadFileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }

    private void writeSevenDetail(jxl.write.WritableSheet ws, CaseSevenModel model, int row) {
        jxl.write.Label labela = new jxl.write.Label(0, row, model.getTitle(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(1, row, model.getContent(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(2, row, deNull(model.getUploadFileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeEightDetail(jxl.write.WritableSheet ws, CaseEightModel model, int row) {
       jxl.write.Label labela = new jxl.write.Label(0, row, model.getTitle(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(1, row, model.getContent(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(2, row, deNull(model.getUploadFileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }
    private void writeNineDetail(jxl.write.WritableSheet ws, CaseNineModel model, int row) {
       jxl.write.Label labela = new jxl.write.Label(0, row, model.getTitle(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(1, row, model.getContent(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(2, row, deNull(model.getUploadFileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }
     private void writeTenDetail(jxl.write.WritableSheet ws, CaseTenModel model, int row) {
       jxl.write.Label labela = new jxl.write.Label(0, row, model.getTitle(), getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(1, row, model.getContent(), getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(2, row, deNull(model.getUploadFileName()), getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeOneSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labele = new jxl.write.Label(0, 2, "被告人姓名", getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(1, 2, "性别", getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(2, 2, "年龄", getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(3, 2, "民族", getCell(true));
        jxl.write.Label labeli = new jxl.write.Label(4, 2, "文化", getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(5, 2, "工作单位", getCell(true));
//        jxl.write.Label labelk = new jxl.write.Label(6, 2, "职务", getCell(true));
        jxl.write.Label labell = new jxl.write.Label(6, 2, "职级", getCell(true));
        jxl.write.Label labelm = new jxl.write.Label(7, 2, "政治面貌", getCell(true));
        jxl.write.Label labeln = new jxl.write.Label(8, 2, "是否人大代表", getCell(true));
        jxl.write.Label labelo = new jxl.write.Label(9, 2, "是否政协委员", getCell(true));
        jxl.write.Label labelp = new jxl.write.Label(10, 2, "岗位", getCell(true));
        jxl.write.Label labelq = new jxl.write.Label(11, 2, "所属行业领域", getCell(true));
        jxl.write.Label labelr = new jxl.write.Label(12, 2, "是否重点热点行业领域", getCell(true));
        jxl.write.Label labels = new jxl.write.Label(13, 2, "案件来源", getCell(true));
        jxl.write.Label labelt = new jxl.write.Label(14, 2, "受案日期", getCell(true));
        jxl.write.Label labelu = new jxl.write.Label(15, 2, "受案案由", getCell(true));
        jxl.write.Label labelv = new jxl.write.Label(16, 2, "初查日期", getCell(true));
        jxl.write.Label labelw = new jxl.write.Label(17, 2, "初查结果", getCell(true));
        jxl.write.Label labelaa = new jxl.write.Label(18, 2, "立案日期", getCell(true));
        jxl.write.Label labelab = new jxl.write.Label(19, 2, "立案数额", getCell(true));
        jxl.write.Label labelac = new jxl.write.Label(20, 2, "侦结日期", getCell(true));
        jxl.write.Label labelad = new jxl.write.Label(21, 2, "侦结案由", getCell(true));
        jxl.write.Label labelae = new jxl.write.Label(22, 2, "侦结数额", getCell(true));
        jxl.write.Label labelaf = new jxl.write.Label(23, 2, "侦结处理结果", getCell(true));
        jxl.write.Label labelag = new jxl.write.Label(24, 2, "移送起诉日期", getCell(true));
        jxl.write.Label labelah = new jxl.write.Label(25, 2, "提起公诉日期", getCell(true));
        jxl.write.Label labelas = new jxl.write.Label(26, 2, "判决日期", getCell(true));
        jxl.write.Label labelai = new jxl.write.Label(27, 2, "判决罪名", getCell(true));
        jxl.write.Label labelaj = new jxl.write.Label(28, 2, "判决认定金额", getCell(true));
        jxl.write.Label labelak = new jxl.write.Label(29, 2, "刑罚", getCell(true));
        jxl.write.Label labelal = new jxl.write.Label(30, 2, "判决认定事实", getCell(true));
        jxl.write.Label labelam = new jxl.write.Label(31, 2, "首次作案日期", getCell(true));
        jxl.write.Label labelan = new jxl.write.Label(32, 2, "末次作案日期", getCell(true));
        jxl.write.Label labelao = new jxl.write.Label(33, 2, "作案次数", getCell(true));
        jxl.write.Label labelap = new jxl.write.Label(34, 2, "作案手段", getCell(true));
        jxl.write.Label labelaq = new jxl.write.Label(35, 2, "发案环节", getCell(true));
        jxl.write.Label labelar = new jxl.write.Label(36, 2, "发案原因", getCell(true));
        jxl.write.Label labelat = new jxl.write.Label(37, 2, "预警发布内容", getCell(true));
        jxl.write.Label labelav = new jxl.write.Label(38, 2, "预警反馈意见", getCell(true));


        try {
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labeli);
            ws.addCell(labelj);
//            ws.addCell(labelk);
            ws.addCell(labell);
            ws.addCell(labelm);
            ws.addCell(labeln);
            ws.addCell(labelo);
            ws.addCell(labelp);
            ws.addCell(labelq);
            ws.addCell(labelr);
            ws.addCell(labels);
            ws.addCell(labelt);
            ws.addCell(labelu);
            ws.addCell(labelv);
            ws.addCell(labelw);
            ws.addCell(labelaa);
            ws.addCell(labelab);
            ws.addCell(labelac);
            ws.addCell(labelad);
            ws.addCell(labelae);
            ws.addCell(labelaf);
            ws.addCell(labelag);
            ws.addCell(labelah);
            ws.addCell(labelai);
            ws.addCell(labelaj);
            ws.addCell(labelak);
            ws.addCell(labelal);
            ws.addCell(labelam);
            ws.addCell(labelan);
            ws.addCell(labelao);
            ws.addCell(labelap);
            ws.addCell(labelaq);
            ws.addCell(labelar);
            ws.addCell(labelas);
            ws.addCell(labelat);
            ws.addCell(labelav);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeTwoSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 1, "信息来源", getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, 1, "责任人", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, 1, "单位", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, 1, "岗位", getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, 1, "违法违纪事实依据", getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, 1, "预警发布内容", getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(6, 1, "预警反馈意见", getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeThreeSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 2, "被举报人姓名", getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, 2, "性别", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, 2, "年龄", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, 2, "民族", getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, 2, "文化", getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, 2, "工作单位", getCell(true));
//        jxl.write.Label labelg = new jxl.write.Label(6, 2, "职务", getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(6, 2, "职级", getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(7, 2, "是否监察对象", getCell(true));
        jxl.write.Label labelk = new jxl.write.Label(8, 2, "所属类别", getCell(true));
        jxl.write.Label labell = new jxl.write.Label(9, 2, "是否人大代表", getCell(true));
        jxl.write.Label labelm = new jxl.write.Label(10, 2, "是否政协委员", getCell(true));
        jxl.write.Label labeln = new jxl.write.Label(11, 2, "岗位", getCell(true));
        jxl.write.Label labelo = new jxl.write.Label(12, 2, "所属行业领域", getCell(true));
        jxl.write.Label labelp = new jxl.write.Label(13, 2, "是否重点热点行业领域", getCell(true));
        jxl.write.Label labelr = new jxl.write.Label(14, 2, "涉案性质", getCell(true));
        jxl.write.Label labels = new jxl.write.Label(15, 2, "处理结果", getCell(true));
        jxl.write.Label labelt = new jxl.write.Label(16, 2, "涉案事实", getCell(true));
        jxl.write.Label labelu = new jxl.write.Label(17, 2, "作案手段", getCell(true));
        jxl.write.Label labelv = new jxl.write.Label(18, 2, "发案环节", getCell(true));
        jxl.write.Label labelw = new jxl.write.Label(19, 2, "发案原因", getCell(true));
        jxl.write.Label labelx = new jxl.write.Label(20, 2, "上传文件", getCell(true));
        jxl.write.Label labely = new jxl.write.Label(21, 2, "预警发布内容", getCell(true));
        jxl.write.Label labelz = new jxl.write.Label(22, 2, "预警反馈意见", getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
//            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labelj);
            ws.addCell(labelk);
            ws.addCell(labell);
            ws.addCell(labelm);
            ws.addCell(labeln);
            ws.addCell(labelo);
            ws.addCell(labelp);
            ws.addCell(labelr);
            ws.addCell(labels);
            ws.addCell(labelt);
            ws.addCell(labelu);
            ws.addCell(labelv);
            ws.addCell(labelw);
            ws.addCell(labelx);
            ws.addCell(labely);
            ws.addCell(labelz);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeFourSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 1, "发现部门", getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, 1, "信息来源", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, 1, "责任人", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, 1, "单位", getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, 1, "岗位", getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, 1, "职务犯罪及和违法违纪事实法律法规依据", getCell(true));
        jxl.write.Label labelj = new jxl.write.Label(6, 1, "上传文件", getCell(true));
        jxl.write.Label labely = new jxl.write.Label(7, 1, "预警发布内容", getCell(true));
        jxl.write.Label labelz = new jxl.write.Label(8, 1, "预警反馈意见", getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelj);
            ws.addCell(labely);
            ws.addCell(labelz);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeFiveSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 1, "被举报人", getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, 1, "单位", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, 1, "岗位", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, 1, "涉案性质", getCell(true));
        jxl.write.Label labele = new jxl.write.Label(4, 1, "举报时间", getCell(true));
        jxl.write.Label labelf = new jxl.write.Label(5, 1, "举报事实", getCell(true));
        jxl.write.Label labelg = new jxl.write.Label(6, 1, "是否检察机关介入开始初查这一情况并接触嫌疑人", getCell(true));
        jxl.write.Label labelh = new jxl.write.Label(7, 1, "是否立案成功", getCell(true));
        jxl.write.Label labeli = new jxl.write.Label(8, 1, "上传文件", getCell(true));
        jxl.write.Label labely = new jxl.write.Label(9, 1, "预警发布内容", getCell(true));
        jxl.write.Label labelz = new jxl.write.Label(10, 1, "预警反馈意见", getCell(true));

        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
            ws.addCell(labele);
            ws.addCell(labelf);
            ws.addCell(labelg);
            ws.addCell(labelh);
            ws.addCell(labeli);
            ws.addCell(labely);
            ws.addCell(labelz);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void writeSixSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 1, "标题", getCell(true));
        jxl.write.Label labelb = new jxl.write.Label(1, 1, "工作单位", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(2, 1, "内容", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(3, 1, "上传文件", getCell(true));
        try {
            ws.addCell(labela);
            ws.addCell(labelb);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

     private void writeSevenSubHeader(jxl.write.WritableSheet ws) {
        jxl.write.Label labela = new jxl.write.Label(0, 1, "标题", getCell(true));
        jxl.write.Label labelc = new jxl.write.Label(1, 1, "内容", getCell(true));
        jxl.write.Label labeld = new jxl.write.Label(2, 1, "上传文件", getCell(true));
        try {
            ws.addCell(labela);
            ws.addCell(labelc);
            ws.addCell(labeld);
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }


//    private void writeSevenSubHeader(jxl.write.WritableSheet ws) {
//        jxl.write.Label labela = new jxl.write.Label(0, 1, "标题", getCell(true));
//        jxl.write.Label labelb = new jxl.write.Label(1, 1, "区域行业职务犯罪状况", getCell(true));
//        jxl.write.Label labelc = new jxl.write.Label(2, 1, "区域行业职务犯罪状况上传文件", getCell(true));
//        jxl.write.Label labeld = new jxl.write.Label(3, 1, "预防职务犯罪政策规定", getCell(true));
//        jxl.write.Label labele = new jxl.write.Label(4, 1, "预防职务犯罪政策规定上传文件", getCell(true));
//
//        try {
//            ws.addCell(labela);
//            ws.addCell(labelb);
//            ws.addCell(labelc);
//            ws.addCell(labeld);
//            ws.addCell(labele);
//        } catch (WriteException e) {
//            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
//        }
//    }

//    private void writeEightSubHeader(jxl.write.WritableSheet ws) {
//        jxl.write.Label labela = new jxl.write.Label(0, 1, "标题", getCell(true));
//        jxl.write.Label labelb = new jxl.write.Label(1, 1, "外地职务犯罪案件信息", getCell(true));
//        jxl.write.Label labelc = new jxl.write.Label(2, 1, "外地职务犯罪案件信息上传文件", getCell(true));
//        jxl.write.Label labeld = new jxl.write.Label(3, 1, "典型预防经验", getCell(true));
//        jxl.write.Label labele = new jxl.write.Label(4, 1, "典型预防经验上传文件", getCell(true));
//        jxl.write.Label labelf = new jxl.write.Label(5, 1, "预防动态", getCell(true));
//        jxl.write.Label labelg = new jxl.write.Label(6, 1, "预防动态上传文件", getCell(true));
//        jxl.write.Label labelh = new jxl.write.Label(7, 1, "新型职务犯罪", getCell(true));
//        jxl.write.Label labeli = new jxl.write.Label(8, 1, "新型职务犯罪上传文件", getCell(true));
//
//        try {
//            ws.addCell(labela);
//            ws.addCell(labelb);
//            ws.addCell(labelc);
//            ws.addCell(labeld);
//            ws.addCell(labele);
//            ws.addCell(labelf);
//            ws.addCell(labelg);
//            ws.addCell(labelh);
//            ws.addCell(labeli);
//        } catch (WriteException e) {
//            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
//        }
//    }

    private void writeHeader(jxl.write.WritableSheet ws, int i) {
        try {
            if (i == 1) {
                jxl.write.Label labela = new jxl.write.Label(4, 0, "检察机关查办的职务犯罪案件信息", getHeader(true));

                ws.mergeCells(4, 0, 9, 0);
                //sub header
                jxl.write.Label labelb = new jxl.write.Label(0, 1, "被告人基本情况", getHeader(false));
                jxl.write.Label labelc = new jxl.write.Label(14, 1, "诉讼情况", getHeader(false));
                jxl.write.Label labeld = new jxl.write.Label(27, 1, "犯罪事实", getHeader(false));
                jxl.write.Label labele = new jxl.write.Label(37, 1, "预警信息", getHeader(false));

                ws.mergeCells(0, 1, 13, 1);
                ws.mergeCells(14, 1, 26, 1);
                ws.mergeCells(27, 1, 36, 1);
                ws.mergeCells(37, 1, 38, 1);
                ws.addCell(labela);
                ws.addCell(labelb);
                ws.addCell(labelc);
                ws.addCell(labeld);
                ws.addCell(labele);
                writeOneSubHeader(ws);
            } else if (i == 2) {
                jxl.write.Label labela = new jxl.write.Label(4, 0, "检察机关办案中发现的尚未构成职务犯罪但明显违法违纪的案件信息", getHeader(true));
                ws.addCell(labela);
                ws.mergeCells(4, 0, 15, 0);
                writeTwoSubHeader(ws);
            } else if (i == 3) {
                jxl.write.Label labelA = new jxl.write.Label(4, 0, "纪检监察机关已立案查处的违法违纪案件信息", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(4, 0, 11, 0);

                jxl.write.Label labelb = new jxl.write.Label(0, 1, "个人基本情况", getHeader(false));
                jxl.write.Label labelc = new jxl.write.Label(15, 1, "违法违纪事实情况", getHeader(false));
                jxl.write.Label labeld = new jxl.write.Label(21, 1, "预警信息", getHeader(false));

                ws.mergeCells(0, 1, 14, 1);
                ws.mergeCells(15, 1, 20, 1);
                ws.mergeCells(21, 1, 22, 1);

                ws.addCell(labelb);
                ws.addCell(labelc);
                ws.addCell(labeld);

                writeThreeSubHeader(ws);
            } else if (i == 4) {
                jxl.write.Label labelA = new jxl.write.Label(4, 0, "财政、审计、信访、公安、法院、工商、税务等机关和部门发现的违法违纪信息", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(4, 0, 17, 0);
                writeFourSubHeader(ws);
            } else if (i == 5) {
                jxl.write.Label labelA = new jxl.write.Label(4, 0, "群众有明确反映的违法违纪信息", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(4, 0, 9, 0);
                writeFiveSubHeader(ws);
            } else if (i == 6) {
                jxl.write.Label labelA = new jxl.write.Label(1, 0, "违法违纪舆情信息", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(1, 0, 4, 0);
                writeSixSubHeader(ws);
            } else if (i == 7) {
                jxl.write.Label labelA = new jxl.write.Label(1, 0, "预防职务犯罪动态", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(1, 0, 4, 0);
                writeSevenSubHeader(ws);
            } else if (i == 8) {
                jxl.write.Label labelA = new jxl.write.Label(1, 0, "预防职务犯罪经验", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(1, 0, 4, 0);
                writeSevenSubHeader(ws);
            }
             else if (i == 9) {
                jxl.write.Label labelA = new jxl.write.Label(1, 0, "预防犯罪动向趋势", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(1, 0, 4, 0);
                writeSevenSubHeader(ws);
            }
             else if (i == 10) {
                jxl.write.Label labelA = new jxl.write.Label(1, 0, "职务犯罪法律法规", getHeader(true));
                ws.addCell(labelA);
                ws.mergeCells(1, 0, 4, 0);
                writeSevenSubHeader(ws);
            }
        } catch (WriteException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private WritableCellFormat getHeader(boolean isTitle) {
        int fontsize = 12;
        if (isTitle) {
            fontsize = 18;
        }
        WritableFont font = new WritableFont(WritableFont.TIMES, fontsize, WritableFont.BOLD);//��������
        try {
            font.setColour(Colour.BLACK);//��ɫ����
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        WritableCellFormat format = new WritableCellFormat(font);
        try {
            format.setAlignment(jxl.format.Alignment.CENTRE);//����
            format.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//���¾���
            if (!isTitle) {
                format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//��ɫ�߿�
            }
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }


    private String getTempFileName(String extension) {
        Date cur = new Date();
        Calendar cld = Calendar.getInstance();
        cld.setTime(cur);
        int mth = cld.get(Calendar.MONTH) + 1;
        String nowStr = cld.get(Calendar.YEAR) + "-" + mth + "-" + cld.get(Calendar.DATE) + "-" + cld.get(Calendar.HOUR) + "-" + cld.get(Calendar.MINUTE) + "-" + cld.get(Calendar.SECOND) + "-";
        Random rdm = new Random();
        String rdmNum = String.valueOf(rdm.nextInt());
        return nowStr + rdmNum + extension;
    }

    private WritableCellFormat getCell(boolean haveBorder) {
        WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);

        WritableCellFormat format = new WritableCellFormat(font);

        try {

            format.setAlignment(jxl.format.Alignment.LEFT);

            format.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);
            if (haveBorder) {
                format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//��ɫ�߿�
            }
            format.setWrap(true);

        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    public byte[] genereateCaseOneColumnChart(String name, ArrayList list,Date startDate,Date endDate) {
        byte[] buf = genereateColumnChart(initialOneReportTO(name, list,startDate,endDate));
        return buf;
    }

    public byte[] genereateCaseOnePieChart(String name, ArrayList list,Date startDate,Date endDate) {
        byte[] buf = genereatePieChart(initialOneReportTO(name, list,startDate,endDate));
        return buf;
    }

    public byte[] genereateCaseThreeColumnChart(String name, ArrayList list,Date startDate,Date endDate) {
        byte[] buf = genereateColumnChart(initialThreeReportTO(name, list,startDate,endDate));
        return buf;
    }

    public byte[] genereateCaseThreePieChart(String name, ArrayList list,Date startDate,Date endDate) {
        byte[] buf = genereatePieChart(initialThreeReportTO(name, list,startDate,endDate));
        return buf;
    }

    private ReportDataTO initialThreeReportTO(String name, ArrayList list,Date startDate,Date endDate) {
        ReportDataTO rptTO = new ReportDataTO();
        if (list != null) {
            Map<String, Integer> ht = new Hashtable<String, Integer>();
            Iterator itor = list.iterator();
            while (itor.hasNext()) {
                String value = (String) itor.next();
                SearchThreeTO searchTO = new SearchThreeTO();
                if ("rank".equals(name)) {
                    searchTO.setRank(value);
                    value = getDescByCode(RANK_CODE, value);
                } else if ("industry".equals(name)) {
                    searchTO.setIndustry(value);
                    value = getDescByCode(CASE_WORK_POS_LIST, value);
                } else if ("hotInd".equals(name)) {
                    searchTO.setHotInd(value);
                    value = getDescByCode(TRUE_CODE, value);
                } else if ("age".equals(name)) {
                    String[] s = value.split("-");
                    searchTO.setAgeFrom(Integer.valueOf(s[0]).intValue());
                    searchTO.setAgeTo(Integer.valueOf(s[1]).intValue());
                }
                searchTO.setDateFrom(startDate);
                searchTO.setDateTo(endDate);
                int j = caseThreeMgr.getCaseThreeReportList(searchTO);
                ht.put(value, j);
            }
            rptTO.setHt(ht);
            rptTO = setReportDataTO(rptTO, name);
        }
        return rptTO;
    }

    private ReportDataTO initialOneReportTO(String name, ArrayList list,Date startDate,Date endDate) {
        ReportDataTO rptTO = new ReportDataTO();


        if (list != null) {
            Map<String, Integer> ht = new Hashtable<String, Integer>();
            Iterator itor = list.iterator();
            while (itor.hasNext()) {
                String value = (String) itor.next();
                SearchOneTO searchTO = new SearchOneTO();
                if ("rank".equals(name)) {
                    searchTO.setRank(value);
                    value = getDescByCode(RANK_CODE, value);
                } else if ("industry".equals(name)) {
                    searchTO.setIndustry(value);
                    value = getDescByCode(CASE_WORK_POS_LIST, value);
                } else if ("hotInd".equals(name)) {
                    searchTO.setHotInd(value);
                    value = getDescByCode(TRUE_CODE, value);
                } else if ("judgeAccusal".equals(name)) {
                    searchTO.setJudgeAccusal(value);
                    value = getDescByCode(CASE_REASON_CODE, value);
                } else if ("age".equals(name)) {
                    String[] s = value.split("-");
                    searchTO.setAgeFrom(Integer.valueOf(s[0]).intValue());
                    searchTO.setAgeTo(Integer.valueOf(s[1]).intValue());
                    if (Integer.valueOf(s[0]).intValue() != 0) {
                        value = String.valueOf(Integer.valueOf(s[0]).intValue() + 1) + "-" + String.valueOf(Integer.valueOf(s[1]).intValue());
                    }
                } else if ("invAmount".equals(name)) {
                    String[] s = value.split("-");
                    searchTO.setInvAmountFrom(Integer.valueOf(s[0]).intValue());
                    searchTO.setInvAmountTo(Integer.valueOf(s[1]).intValue());
                    if (Integer.valueOf(s[0]).intValue() != 0) {
                        value = String.valueOf(Integer.valueOf(s[0]).intValue() + 1) + "-" + String.valueOf(Integer.valueOf(s[1]).intValue());
                    }
                }
                searchTO.setDateFrom(startDate);
                searchTO.setDateTo(endDate);
                int j = caseOneMgr.getCaseOneReportList(searchTO);
                ht.put(value, j);
            }
            rptTO.setHt(ht);
            rptTO = setReportDataTO(rptTO, name);
        }


        return rptTO;
    }

    private ReportDataTO setReportDataTO(ReportDataTO rptTO, String name) {
        rptTO.setFontstyle("隶书");
        if ("rank".equals(name)) {
            rptTO.setTitle("职级统计图");
            rptTO.setColumnh("职级");
            rptTO.setColumnv("个数");
        } else if ("industry".equals(name)) {
            rptTO.setTitle("所属行业领域统计图");
            rptTO.setColumnh("领域");
            rptTO.setColumnv("个数");
        } else if ("hotInd".equals(name)) {
            rptTO.setTitle("是否重点热点行业领域统计图");
            rptTO.setColumnh("是否重点热点行业领域");
            rptTO.setColumnv("个数");
        } else if ("judgeAccusal".equals(name)) {
            rptTO.setTitle("判决罪名统计图");
            rptTO.setColumnh("判决罪名");
            rptTO.setColumnv("个数");
        }
        if ("age".equals(name)) {
            rptTO.setTitle("年龄统计图");
            rptTO.setColumnh("年龄(岁)");
            rptTO.setColumnv("个数");
        }
        if ("invAmount".equals(name)) {
            rptTO.setTitle("侦结数额统计图");
            rptTO.setColumnh("侦结数额(元)");
            rptTO.setColumnv("个数");
        }
        return rptTO;

    }

    public byte[] genereateColumnChart(ReportDataTO rptTO) {
        byte[] buf = new byte[0];
        Map<String, Integer> ht = rptTO.getHt();
        // 设定数据源




        DefaultCategoryDataset dataset = new DefaultCategoryDataset();

        // 向数据源中插值，第一个参数为名称，第二个参数是double数




        int i = 0;
        for (String nation : ht.keySet()) {
            i++;
            dataset.addValue((int) ht.get(nation), String.valueOf(i), nation);
        }

        // 使用ChartFactory来创建JFreeChart

        JFreeChart chart = ChartFactory.createBarChart3D(rptTO.getTitle(), rptTO.getColumnh(),
                rptTO.getColumnv(), dataset, PlotOrientation.VERTICAL, false, false,
                false);
        CategoryPlot plot = chart.getCategoryPlot();
        NumberAxis vn = (NumberAxis) plot.getRangeAxis();
        vn.setTickUnit(new NumberTickUnit(1D));
        // 设定图片标题
        chart.setTitle(new TextTitle(rptTO.getTitle(), new Font(rptTO.getFontstyle(), Font.ITALIC, 15)));

        // 设定背景
        chart.setBackgroundPaint(Color.white);

        // 创建文件并将图片地址传递到页面
        String fileName = getTempFileName(".png");
        File file1 = new File(FOLDER);
        if (!file1.exists()) {
            file1.mkdir();
        }
        File file = new File(FOLDER + fileName);
        try {
            ChartUtilities.saveChartAsPNG(file, chart, 800, 250);
            buf = readByteArrayOfSpecifiedFile(file);
             file.delete();
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return buf;
    }


    public byte[] genereatePieChart(ReportDataTO rptTO) {
        byte[] buf = new byte[0];
        Map<String, Integer> ht = rptTO.getHt();
        float sum = 0;
        for (Integer i : ht.values()) {
            sum += i;
        }
        // 设定数据源




        DefaultKeyedValuesDataset piedata = new DefaultKeyedValuesDataset();

        // 向数据源中插值，第一个参数为名称，第二个参数是double数




        for (String nation : ht.keySet()) {
            piedata.setValue(nation, ((float) ht.get(nation)) / sum * 100);
        }

        // 使用ChartFactory来创建JFreeChart
        JFreeChart chart = ChartFactory.createPieChart(rptTO.getTitle(), piedata, true,
                true, true);

        // 设定图片标题
        chart.setTitle(new TextTitle(rptTO.getTitle(), new Font(rptTO.getFontstyle(), Font.ITALIC, 15)));

        // 设定背景
        chart.setBackgroundPaint(Color.white);

        // 创建文件并将图片地址传递到页面
        String fileName = getTempFileName(".png");
        File file1 = new File(FOLDER);
        if (!file1.exists()) {
            file1.mkdir();
        }
        File file = new File(FOLDER + fileName);
        try {
            ChartUtilities.saveChartAsPNG(file, chart, 800, 250);
            buf = readByteArrayOfSpecifiedFile(file);
            file.delete();
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return buf;
    }

    public String getDescByCode(String codeType, String codeId) {
        if (codeId == null || "".equals(codeId)) {
            return "";
        }
        return codeLookupMgr.getCodeDesc(codeType, codeId);
    }

    public String getIndutryDescByCode(String codeId1, String codeId2) {
        String result = "";
        if (codeId1 == null || "".equals(codeId1) || codeId2 == null || "".equals(codeId2)) {
            return "";
        }
        //CodeModel codeModel1 = codeLookupMgr.getCode(CASE_WORK_POS_LIST,codeId1);
        String result1 = codeLookupMgr.getCodeDesc(CASE_WORK_POS_LIST, codeId1);
        String codeType = "";
        if ("1".equals(codeId1)) {
            codeType = CASE_WORK_POS_LIST1;
        } else if ("2".equals(codeId1)) {
            codeType = CASE_WORK_POS_LIST2;
        } else if ("3".equals(codeId1)) {
            codeType = CASE_WORK_POS_LIST3;
        }

        result = result1 + "-" + codeLookupMgr.getCodeDesc(codeType, codeId2);

        return result;
    }

    public String deNull(String c) {
        if (c == null) {
            return "";
        }
        return c;
    }
}

